﻿Imports SS
Imports SS.Databases
Imports SS.Databases.Connecter
Imports SS.ApplicationBlock

Public Class Point

    Dim connOra As DBOracle
    Public ReadOnly Property CurrentConnection As DBOracle
        Get
            Return connOra
        End Get
    End Property

    Public Sub New()

    End Sub

    Public Sub New(ByVal Transaction As Boolean)
        connOra = New DBOracle(Common.Utility.ConnectString, Transaction)
    End Sub

    Private Shared Function Excute(ByVal sql As String) As DataTable
        Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
            Return connOraSelect.GetDataTable(sql, "Data")
        End Using
    End Function

    Public Sub Commit()
        connOra.Commit()
        connOra.Close()
    End Sub

    Public Sub RollBack()
        connOra.Rollback()
        connOra.Close()
    End Sub

#Region "Point"

    Public Shared Function GetPoint(ByVal info As Entity.PointItem) As Data.DataTable
        Dim sb As New Text.StringBuilder
        sb.AppendLine("SELECT * ")
        sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_CONFIG_CALCULATE_POINT")
        sb.AppendLine("WHERE 1=1 ")

        If Not IsNothing(info.point_id) Then
            sb.AppendLine("AND RNWM_CONFIG_CALCULATE_POINT.POINT_ID = " & info.point_id)
        End If

        If Not IsNothing(info.point_start_date) Then
            sb.AppendLine("AND TO_CHAR(RNWM_CONFIG_CALCULATE_POINT.POINT_START_DATE,'YYYYMMDD') >= '" & info.point_start_date.Value.ToString("yyyyMMdd") & "'")
        End If

        If Not IsNothing(info.point_start_date_to) Then
            sb.AppendLine("AND TO_CHAR(RNWM_CONFIG_CALCULATE_POINT.POINT_START_DATE,'YYYYMMDD') <= '" & info.point_start_date_to.Value.ToString("yyyyMMdd") & "'")
        End If

        If Not IsNothing(info.point_end_date) Then
            sb.AppendLine("AND TO_CHAR(RNWM_CONFIG_CALCULATE_POINT.POINT_END_DATE,'YYYYMMDD') >= '" & info.point_end_date.Value.ToString("yyyyMMdd") & "'")
        End If

        If Not IsNothing(info.point_end_date_to) Then
            sb.AppendLine("AND TO_CHAR(RNWM_CONFIG_CALCULATE_POINT.POINT_END_DATE,'YYYYMMDD') <= '" & info.point_end_date_to.Value.ToString("yyyyMMdd") & "'")
        End If

        If Not String.IsNullOrEmpty(info.point_name) AndAlso info.point_name <> "" Then
            sb.AppendLine("AND RNWM_CONFIG_CALCULATE_POINT.POINT_NAME LIKE '%" & info.point_name & "%'")
        End If

        If Not IsNothing(info.opposite_id) Then
            sb.AppendLine("AND RNWM_CONFIG_CALCULATE_POINT.POINT_ID <> " & info.opposite_id)
        End If

        sb.AppendLine(" ORDER BY RNWM_CONFIG_CALCULATE_POINT.POINT_START_DATE DESC")

        Return Excute(sb.ToString)
    End Function

    Public Shared Function GetPointById(ByVal info As Decimal) As Entity.PointItem
        Dim sb As New Text.StringBuilder
        sb.AppendLine("SELECT * ")
        sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_CONFIG_CALCULATE_POINT")
        sb.AppendLine("WHERE 1=1 ")

        If Not IsNothing(info) Then
            sb.AppendLine("AND RNWM_CONFIG_CALCULATE_POINT.POINT_ID = " & info)
        End If
        Dim dt As DataTable = New DataTable
        dt = Excute(sb.ToString)
        Dim oInfo As Entity.PointItem = New Entity.PointItem
        If Not IsDBNull(dt.Rows(0)("POINT_ID")) Then oInfo.point_id = dt.Rows(0)("POINT_ID")
        If Not IsDBNull(dt.Rows(0)("POINT_NAME")) Then oInfo.point_name = dt.Rows(0)("POINT_NAME")
        If Not IsDBNull(dt.Rows(0)("POINT_START_DATE")) Then oInfo.point_start_date = Convert.ToDateTime(dt.Rows(0)("POINT_START_DATE")).ToString("dd/MM/yyyy", Common.Utility.CulInfo)
        If Not IsDBNull(dt.Rows(0)("POINT_END_DATE")) Then oInfo.point_end_date = Convert.ToDateTime(dt.Rows(0)("POINT_END_DATE")).ToString("dd/MM/yyyy", Common.Utility.CulInfo)
        If Not IsDBNull(dt.Rows(0)("POINT_LEVEL_1")) Then oInfo.point_level_1 = dt.Rows(0)("POINT_LEVEL_1")
        If Not IsDBNull(dt.Rows(0)("POINT_LEVEL_2")) Then oInfo.point_level_2 = dt.Rows(0)("POINT_LEVEL_2")
        If Not IsDBNull(dt.Rows(0)("POINT_LEVEL_3")) Then oInfo.point_level_3 = dt.Rows(0)("POINT_LEVEL_3")
        If Not IsDBNull(dt.Rows(0)("POINT_LEVEL_4")) Then oInfo.point_level_4 = dt.Rows(0)("POINT_LEVEL_4")
        If Not IsDBNull(dt.Rows(0)("POINT_LEVEL_5")) Then oInfo.point_level_5 = dt.Rows(0)("POINT_LEVEL_5")
        If Not IsDBNull(dt.Rows(0)("POINT_LEVEL_6")) Then oInfo.point_level_6 = dt.Rows(0)("POINT_LEVEL_6")
        If Not IsDBNull(dt.Rows(0)("POINT_LEVEL_7")) Then oInfo.point_level_7 = dt.Rows(0)("POINT_LEVEL_7")
        If Not IsDBNull(dt.Rows(0)("POINT_LEVEL_8")) Then oInfo.point_level_8 = dt.Rows(0)("POINT_LEVEL_8")
        If Not IsDBNull(dt.Rows(0)("POINT_LEVEL_9")) Then oInfo.point_level_9 = dt.Rows(0)("POINT_LEVEL_9")
        If Not IsDBNull(dt.Rows(0)("CREATE_USER_ID")) Then oInfo.create_user_id = dt.Rows(0)("CREATE_USER_ID")
        If Not IsDBNull(dt.Rows(0)("CREATE_DATE")) Then oInfo.create_date = Convert.ToDateTime(dt.Rows(0)("CREATE_DATE")).ToString("dd/MM/yyyy", Common.Utility.CulInfo)
        If Not IsDBNull(dt.Rows(0)("UPD_USER_ID")) Then oInfo.upd_user_id = dt.Rows(0)("UPD_USER_ID")
        If Not IsDBNull(dt.Rows(0)("UPD_DATE")) Then oInfo.upd_date = Convert.ToDateTime(dt.Rows(0)("UPD_DATE")).ToString("dd/MM/yyyy", Common.Utility.CulInfo)
        Return oInfo
    End Function

    Public Function GetNewID() As Decimal
        Dim RVID As Integer = 0
        Dim sb As New Text.StringBuilder
        sb.AppendLine("SELECT * FROM( ")
        sb.AppendLine("Select POINT_ID ")
        sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_CONFIG_CALCULATE_POINT")
        sb.AppendLine("ORDER BY POINT_ID DESC ")
        sb.AppendLine(")WHERE ROWNUM = 1 ")

        Dim dt As DataTable = New DataTable
        dt = Excute(sb.ToString)
        If dt.Rows.Count > 0 Then
            RVID = Convert.ToDecimal(dt.Rows(0)("POINT_ID")) + 1
        Else
            RVID = 1
        End If
        Return RVID
    End Function

    Public Function Insert(ByVal info As Entity.PointItem) As Boolean
        Dim strSql As String
        strSql = ORM.Broker.RetrieveInsert(info, Common.Utility.Schema & ".RNWM_CONFIG_CALCULATE_POINT")
        If connOra.ExecuteNonQuery(strSql) = 1 Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Function Update(ByVal info As Entity.PointItem) As Boolean
        Dim strSql As String
        strSql = ORM.Broker.RetrieveUpdate(info, Common.Utility.Schema & ".RNWM_CONFIG_CALCULATE_POINT", "POINT_ID='" & info.point_id & "'")
        If connOra.ExecuteNonQuery(strSql) = 1 Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Function InsertHistory(ByVal info As Entity.PointItem) As Boolean
        info.history_id = GetNewHistoryID()
        Dim strSql As String
        strSql = ORM.Broker.RetrieveInsert(info, Common.Utility.Schema & ".RNWM_CONFIG_CALCULATE_POINT_H")
        If connOra.ExecuteNonQuery(strSql) = 1 Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Function GetNewHistoryID() As Decimal
        Dim RVID As Integer = 0
        Dim sb As New Text.StringBuilder
        sb.AppendLine("SELECT * FROM( ")
        sb.AppendLine("Select HISTORY_ID ")
        sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_CONFIG_CALCULATE_POINT_H")
        sb.AppendLine("ORDER BY HISTORY_ID DESC ")
        sb.AppendLine(")WHERE ROWNUM = 1 ")

        Dim dt As DataTable = New DataTable
        dt = Excute(sb.ToString)
        If dt.Rows.Count > 0 Then
            RVID = Convert.ToDecimal(dt.Rows(0)("HISTORY_ID")) + 1
        Else
            RVID = 1
        End If
        Return RVID
    End Function

#End Region

End Class
